using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;

using Entities;
using IDAL;

namespace SQLDBDAL
{
    public class CategoryDAO : ICategoryDAO  
    {
        //ProductInfo pro = new ProductInfo();

        private const string SQL_SELECT_CATEGORY = "SELECT * FROM Categories";
        private const string SQL_SELECT_CATEGORY_BY_WAREHOUSE_ID = "SELECT * FROM Categories WHERE WareHouseID=@WareHouseID";
        private const string SQL_SELECT_CATEGORY_BY_ID = "SELECT * FROM Categories WHERE CategoryID = @CategoryID";
        private const string SQL_INSERT_CATEGORY = "INSERT INTO Categories(WareHouseID, CategoryName, Description) VALUES(@WareHouseID, @CategoryName, @Description)";
        private const string SQL_UPDATE_CATEGORY = "UPDATE Categories SET WareHouseID=@WareHouseID, CategoryName=@CategoryName, Description=@Description  WHERE CategoryID=@CategoryID";
        private const string PROCEDURE_DELETE_CATEGORY = "sp_DeleteCategory";
       

        // Parameters
        private const string PARM_CATEGORY_ID = "@CategoryID";
        private const string PARM_PRODUCT_ID = "@ProductID";
        private const string PARM_WAREHOUSE_ID = "@WareHouseID";
        private const string PARM_CATEGORY_NAME = "@CategoryName";
        private const string PARM_DESCRIPTION = "@Description";

        #region ICategoryDAO Members

        public bool Delete(CategoryInfo cate)
        {
            SqlParameter[] parms;
            parms = new SqlParameter[] { new SqlParameter(PARM_CATEGORY_ID, SqlDbType.Int) };
            parms[0].Value = cate.CategoryID;

            try
            {
                int result = SQLDBHelper.ExecuteNonQuery(SQLDBHelper.CONN_STRING, CommandType.StoredProcedure, PROCEDURE_DELETE_CATEGORY, parms);
                return (result > 0 ? true : false);
            }
            catch (Exception e)
            {                
                throw e;
            }
        }

        public IList<CategoryInfo> GetAll()
        {
            IList<CategoryInfo> categories = new List<CategoryInfo>();
            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING,
                CommandType.Text, SQL_SELECT_CATEGORY, null))
            {
                while (reader.Read())
                {
                    CategoryInfo cate = new CategoryInfo();
                    cate.CategoryID = reader.GetInt32(0);
                    cate.WareHouseID = reader.GetInt32(1);
                    cate.CategoryName = reader.GetString(2);                    
                    if (!reader.IsDBNull(3))
                        cate.Description = reader.GetString(3);
                    cate.Products = new ProductDAO().GetByCategoryID(cate.CategoryID.ToString());
                    categories.Add(cate);
                }
            }
            return categories;
        }

        public IList<CategoryInfo> GetByWareHouseID(string wareHouseID)
        {
            SqlParameter[] parms;
            parms = new SqlParameter[] { new SqlParameter(PARM_WAREHOUSE_ID, SqlDbType.Int) };
            parms[0].Value = int.Parse(wareHouseID);

            IList<CategoryInfo> categories = new List<CategoryInfo>();
            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING,
                CommandType.Text, SQL_SELECT_CATEGORY_BY_WAREHOUSE_ID, parms))
            {
                while (reader.Read())
                {
                    CategoryInfo category = new CategoryInfo();
                    category.CategoryID = reader.GetInt32(0);
                    category.WareHouseID = reader.GetInt32(1);
                    category.CategoryName = reader.GetString(2);
                    if (!reader.IsDBNull(3))
                        category.Description = reader.GetString(3);
                    categories.Add(category);
                }
            }
            return categories;
        }

        public CategoryInfo GetByID(string categoryID)
        {
            SqlParameter[] parms = new SqlParameter[] { new SqlParameter(PARM_CATEGORY_ID, SqlDbType.Int) };
            parms[0].Value = categoryID;

            CategoryInfo category = new CategoryInfo();
            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING, CommandType.Text, SQL_SELECT_CATEGORY_BY_ID, parms))
            {
                while (reader.Read())
                {
                    category.CategoryID = reader.GetInt32(0);
                    category.WareHouseID = reader.GetInt32(1);
                    category.CategoryName = reader.GetString(2);
                    if (!reader.IsDBNull(3))
                        category.Description = reader.GetString(3);
                }
            }
            return category;
        }


        public bool Insert(CategoryInfo cate)
        {
            SqlParameter[] parms;
            parms = new SqlParameter[] {
											 new SqlParameter(PARM_WAREHOUSE_ID, SqlDbType.Int),
											 new SqlParameter(PARM_CATEGORY_NAME, SqlDbType.NVarChar),
                                             new SqlParameter(PARM_DESCRIPTION, SqlDbType.NText)};

            parms[0].Value = cate.WareHouseID;
            parms[1].Value = cate.CategoryName;
            parms[2].Value = cate.Description;
           
            try
            {
                int result = 0;
                SqlConnection conn = new SqlConnection(SQLDBHelper.CONN_STRING);
                result = SQLDBHelper.ExecuteNonQuery(conn, CommandType.Text, SQL_INSERT_CATEGORY, parms);
                SQLDBHelper.SetNewIdentity(conn, cate.CategoryID);
                return (result > 0 ? true : false);
            }
            catch (Exception ex)
            {
                throw ex;
            }
           
        }

        public bool Update(CategoryInfo cate)
        {
            SqlParameter[] parms;
            parms = new SqlParameter[] {
											 new SqlParameter(PARM_WAREHOUSE_ID, SqlDbType.Int),
											 new SqlParameter(PARM_CATEGORY_NAME, SqlDbType.NVarChar),
                                             new SqlParameter(PARM_DESCRIPTION, SqlDbType.NText),
                                             new SqlParameter(PARM_CATEGORY_ID, SqlDbType.Int)};

            parms[0].Value = cate.WareHouseID;
            parms[1].Value = cate.CategoryName;
            parms[2].Value = cate.Description;
            parms[3].Value = cate.CategoryID;


            try
            {
                int result = 0;
                result = SQLDBHelper.ExecuteNonQuery(SQLDBHelper.CONN_STRING, CommandType.Text, SQL_UPDATE_CATEGORY, parms);
                return (result > 0 ? true : false);
            }
            catch (Exception ex)
            {
                throw ex;
            }
          
        }

        #endregion
    }
}
